{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "c2cfeb2f-154d-45e9-bea0-4a846ae3bfc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9a6647f3-6ddc-417e-a4ab-f59bbf4f213f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "5f21872f-bad2-43f4-93ea-32b671cc07b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[WindowsPath('stock_trades/202207-湘财.xls'),\n",
       " WindowsPath('stock_trades/202208-湘财.xls'),\n",
       " WindowsPath('stock_trades/202209-湘财.xls'),\n",
       " WindowsPath('stock_trades/202210-湘财.xls'),\n",
       " WindowsPath('stock_trades/202211-湘财.xls'),\n",
       " WindowsPath('stock_trades/202212-湘财.xls'),\n",
       " WindowsPath('stock_trades/202301-湘财.xls'),\n",
       " WindowsPath('stock_trades/202302-湘财.xls'),\n",
       " WindowsPath('stock_trades/202303-湘财.xls'),\n",
       " WindowsPath('stock_trades/202304-湘财.xls'),\n",
       " WindowsPath('stock_trades/202305-湘财.xls'),\n",
       " WindowsPath('stock_trades/202306-湘财.xls'),\n",
       " WindowsPath('stock_trades/202309-湘财.xls')]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[f for f in Path(\"stock_trades/\").glob(\"*-湘财.xls\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "e48a61a8-914e-478b-be35-e5c83ffc718f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def read_df_湘财(f: str | Path) -> pl.DataFrame:\n",
    "    df = pl.read_csv(\n",
    "        f,\n",
    "        encoding=\"gb18030\",\n",
    "        separator=\"\\t\",\n",
    "        infer_schema=False,\n",
    "    )\n",
    "    df = df.with_columns(\n",
    "        pl.selectors.all().str.strip_prefix(\"=\").str.strip_chars('\"'),\n",
    "    ).with_columns(\n",
    "        pl.col(\"发生日期\").str.to_date(\"%Y%m%d\"),\n",
    "        pl.col(\"成交时间\").str.to_time(),\n",
    "        pl.col(\n",
    "            \"成交数量\",\n",
    "            \"成交价格\",\n",
    "            \"成交金额\",\n",
    "            \"发生金额\",\n",
    "            \"手续费\",\n",
    "            \"印花税\",\n",
    "            \"过户费\",\n",
    "            \"其他费\",\n",
    "        ).cast(pl.Float64),\n",
    "    )\n",
    "    df = df.filter(\n",
    "        pl.col(\"业务名称\").is_in([\"证券买入\", \"证券卖出\"]),\n",
    "    )\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "2301c11d-5329-42c1-bc58-646542f544d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = [read_df_湘财(f) for f in Path(\"stock_trades/\").glob(\"*-湘财.xls\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "2e138fa1-1adc-4958-9a5f-628ef7fb6a82",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = pl.concat(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d1d3a838-c604-4330-85bf-12a10d4a28f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (257, 16)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>发生日期</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>业务名称</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th><th>币种</th></tr><tr><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>time</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>2022-07-18</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>09:38:10</td><td>-10400.0</td><td>13.2062</td><td>137344.0</td><td>137184.67</td><td>21.98</td><td>137.35</td><td>1.38</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600408&quot;</td><td>&quot;安泰集团&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:44:52</td><td>47000.0</td><td>3.19</td><td>149930.0</td><td>-149955.5</td><td>23.99</td><td>0.0</td><td>1.51</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600648&quot;</td><td>&quot;外高桥&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:44:31</td><td>11900.0</td><td>12.6066</td><td>150019.0</td><td>-150044.49</td><td>24.0</td><td>0.0</td><td>1.49</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600269&quot;</td><td>&quot;赣粤高速&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:43:38</td><td>40700.0</td><td>3.69</td><td>150183.0</td><td>-150208.53</td><td>24.03</td><td>0.0</td><td>1.5</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600015&quot;</td><td>&quot;华夏银行&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:42:51</td><td>30000.0</td><td>5.07</td><td>152100.0</td><td>-152125.86</td><td>24.34</td><td>0.0</td><td>1.52</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-06-19</td><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>10:18:46</td><td>-5000.0</td><td>9.13</td><td>45650.0</td><td>45596.59</td><td>7.3</td><td>45.65</td><td>0.46</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2023-06-12</td><td>&quot;300641&quot;</td><td>&quot;正丹股份&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>13:22:32</td><td>-9600.0</td><td>5.05</td><td>48480.0</td><td>48423.76</td><td>7.76</td><td>48.48</td><td>0.48</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2023-06-02</td><td>&quot;000655&quot;</td><td>&quot;金岭矿业&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>10:26:35</td><td>-7500.0</td><td>6.48</td><td>48600.0</td><td>48543.61</td><td>7.78</td><td>48.61</td><td>0.47</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2023-06-01</td><td>&quot;300214&quot;</td><td>&quot;日科化学&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>10:45:35</td><td>-7400.0</td><td>6.8</td><td>50320.0</td><td>50261.63</td><td>8.05</td><td>50.32</td><td>0.51</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td></tr><tr><td>2023-09-11</td><td>&quot;603577&quot;</td><td>&quot;汇金通&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>09:46:10</td><td>-5100.0</td><td>9.3024</td><td>47442.0</td><td>47410.89</td><td>6.9</td><td>23.72</td><td>0.49</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (257, 16)\n",
       "┌────────────┬──────────┬──────────┬──────────┬───┬────────┬────────┬──────────┬────────┐\n",
       "│ 发生日期   ┆ 证券代码 ┆ 证券名称 ┆ 买卖标志 ┆ … ┆ 过户费 ┆ 其他费 ┆ 备注     ┆ 币种   │\n",
       "│ ---        ┆ ---      ┆ ---      ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---      ┆ ---    │\n",
       "│ date       ┆ str      ┆ str      ┆ str      ┆   ┆ f64    ┆ f64    ┆ str      ┆ str    │\n",
       "╞════════════╪══════════╪══════════╪══════════╪═══╪════════╪════════╪══════════╪════════╡\n",
       "│ 2022-07-18 ┆ 002462   ┆ 嘉事堂   ┆ 卖出     ┆ … ┆ 1.38   ┆ 0.0    ┆ 证券卖出 ┆ 人民币 │\n",
       "│ 2022-07-18 ┆ 600408   ┆ 安泰集团 ┆ 买入     ┆ … ┆ 1.51   ┆ 0.0    ┆ 证券买入 ┆ 人民币 │\n",
       "│ 2022-07-18 ┆ 600648   ┆ 外高桥   ┆ 买入     ┆ … ┆ 1.49   ┆ 0.0    ┆ 证券买入 ┆ 人民币 │\n",
       "│ 2022-07-18 ┆ 600269   ┆ 赣粤高速 ┆ 买入     ┆ … ┆ 1.5    ┆ 0.0    ┆ 证券买入 ┆ 人民币 │\n",
       "│ 2022-07-18 ┆ 600015   ┆ 华夏银行 ┆ 买入     ┆ … ┆ 1.52   ┆ 0.0    ┆ 证券买入 ┆ 人民币 │\n",
       "│ …          ┆ …        ┆ …        ┆ …        ┆ … ┆ …      ┆ …      ┆ …        ┆ …      │\n",
       "│ 2023-06-19 ┆ 603967   ┆ 中创物流 ┆ 卖出     ┆ … ┆ 0.46   ┆ 0.0    ┆ 证券卖出 ┆ 人民币 │\n",
       "│ 2023-06-12 ┆ 300641   ┆ 正丹股份 ┆ 卖出     ┆ … ┆ 0.48   ┆ 0.0    ┆ 证券卖出 ┆ 人民币 │\n",
       "│ 2023-06-02 ┆ 000655   ┆ 金岭矿业 ┆ 卖出     ┆ … ┆ 0.47   ┆ 0.0    ┆ 证券卖出 ┆ 人民币 │\n",
       "│ 2023-06-01 ┆ 300214   ┆ 日科化学 ┆ 卖出     ┆ … ┆ 0.51   ┆ 0.0    ┆ 证券卖出 ┆ 人民币 │\n",
       "│ 2023-09-11 ┆ 603577   ┆ 汇金通   ┆ 卖出     ┆ … ┆ 0.49   ┆ 0.0    ┆ 证券卖出 ┆ 人民币 │\n",
       "└────────────┴──────────┴──────────┴──────────┴───┴────────┴────────┴──────────┴────────┘"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "1d1d93b8-cb00-45e5-8f3a-08a79a130a95",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = d1.with_columns(\n",
    "    券商=pl.lit(\"湘财\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "2fe5429c-7342-4fe1-8a4c-41c952c20a8b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (257, 17)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>发生日期</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>业务名称</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th><th>币种</th><th>券商</th></tr><tr><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>time</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>2022-07-18</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>09:38:10</td><td>-10400.0</td><td>13.2062</td><td>137344.0</td><td>137184.67</td><td>21.98</td><td>137.35</td><td>1.38</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600408&quot;</td><td>&quot;安泰集团&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:44:52</td><td>47000.0</td><td>3.19</td><td>149930.0</td><td>-149955.5</td><td>23.99</td><td>0.0</td><td>1.51</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600648&quot;</td><td>&quot;外高桥&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:44:31</td><td>11900.0</td><td>12.6066</td><td>150019.0</td><td>-150044.49</td><td>24.0</td><td>0.0</td><td>1.49</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600269&quot;</td><td>&quot;赣粤高速&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:43:38</td><td>40700.0</td><td>3.69</td><td>150183.0</td><td>-150208.53</td><td>24.03</td><td>0.0</td><td>1.5</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2022-07-18</td><td>&quot;600015&quot;</td><td>&quot;华夏银行&quot;</td><td>&quot;买入&quot;</td><td>&quot;证券买入&quot;</td><td>09:42:51</td><td>30000.0</td><td>5.07</td><td>152100.0</td><td>-152125.86</td><td>24.34</td><td>0.0</td><td>1.52</td><td>0.0</td><td>&quot;证券买入&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-06-19</td><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>10:18:46</td><td>-5000.0</td><td>9.13</td><td>45650.0</td><td>45596.59</td><td>7.3</td><td>45.65</td><td>0.46</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2023-06-12</td><td>&quot;300641&quot;</td><td>&quot;正丹股份&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>13:22:32</td><td>-9600.0</td><td>5.05</td><td>48480.0</td><td>48423.76</td><td>7.76</td><td>48.48</td><td>0.48</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2023-06-02</td><td>&quot;000655&quot;</td><td>&quot;金岭矿业&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>10:26:35</td><td>-7500.0</td><td>6.48</td><td>48600.0</td><td>48543.61</td><td>7.78</td><td>48.61</td><td>0.47</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2023-06-01</td><td>&quot;300214&quot;</td><td>&quot;日科化学&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>10:45:35</td><td>-7400.0</td><td>6.8</td><td>50320.0</td><td>50261.63</td><td>8.05</td><td>50.32</td><td>0.51</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr><tr><td>2023-09-11</td><td>&quot;603577&quot;</td><td>&quot;汇金通&quot;</td><td>&quot;卖出&quot;</td><td>&quot;证券卖出&quot;</td><td>09:46:10</td><td>-5100.0</td><td>9.3024</td><td>47442.0</td><td>47410.89</td><td>6.9</td><td>23.72</td><td>0.49</td><td>0.0</td><td>&quot;证券卖出&quot;</td><td>&quot;人民币&quot;</td><td>&quot;湘财&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (257, 17)\n",
       "┌────────────┬──────────┬──────────┬──────────┬───┬────────┬──────────┬────────┬──────┐\n",
       "│ 发生日期   ┆ 证券代码 ┆ 证券名称 ┆ 买卖标志 ┆ … ┆ 其他费 ┆ 备注     ┆ 币种   ┆ 券商 │\n",
       "│ ---        ┆ ---      ┆ ---      ┆ ---      ┆   ┆ ---    ┆ ---      ┆ ---    ┆ ---  │\n",
       "│ date       ┆ str      ┆ str      ┆ str      ┆   ┆ f64    ┆ str      ┆ str    ┆ str  │\n",
       "╞════════════╪══════════╪══════════╪══════════╪═══╪════════╪══════════╪════════╪══════╡\n",
       "│ 2022-07-18 ┆ 002462   ┆ 嘉事堂   ┆ 卖出     ┆ … ┆ 0.0    ┆ 证券卖出 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2022-07-18 ┆ 600408   ┆ 安泰集团 ┆ 买入     ┆ … ┆ 0.0    ┆ 证券买入 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2022-07-18 ┆ 600648   ┆ 外高桥   ┆ 买入     ┆ … ┆ 0.0    ┆ 证券买入 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2022-07-18 ┆ 600269   ┆ 赣粤高速 ┆ 买入     ┆ … ┆ 0.0    ┆ 证券买入 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2022-07-18 ┆ 600015   ┆ 华夏银行 ┆ 买入     ┆ … ┆ 0.0    ┆ 证券买入 ┆ 人民币 ┆ 湘财 │\n",
       "│ …          ┆ …        ┆ …        ┆ …        ┆ … ┆ …      ┆ …        ┆ …      ┆ …    │\n",
       "│ 2023-06-19 ┆ 603967   ┆ 中创物流 ┆ 卖出     ┆ … ┆ 0.0    ┆ 证券卖出 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2023-06-12 ┆ 300641   ┆ 正丹股份 ┆ 卖出     ┆ … ┆ 0.0    ┆ 证券卖出 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2023-06-02 ┆ 000655   ┆ 金岭矿业 ┆ 卖出     ┆ … ┆ 0.0    ┆ 证券卖出 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2023-06-01 ┆ 300214   ┆ 日科化学 ┆ 卖出     ┆ … ┆ 0.0    ┆ 证券卖出 ┆ 人民币 ┆ 湘财 │\n",
       "│ 2023-09-11 ┆ 603577   ┆ 汇金通   ┆ 卖出     ┆ … ┆ 0.0    ┆ 证券卖出 ┆ 人民币 ┆ 湘财 │\n",
       "└────────────┴──────────┴──────────┴──────────┴───┴────────┴──────────┴────────┴──────┘"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "2bddcb36-69fd-41e8-904e-eafbe0de6b62",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 14)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>成交日期</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>操作</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th></tr><tr><td>str</td><td>str</td><td>date</td><td>time</td><td>i64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>f64</td><td>i64</td><td>f64</td><td>i64</td><td>str</td></tr></thead><tbody><tr><td>&quot;600626&quot;</td><td>&quot;申达股份&quot;</td><td>2023-05-24</td><td>10:06:14</td><td>14800</td><td>3.37</td><td>49876.0</td><td>-49881.48</td><td>&quot;买&quot;</td><td>4.99</td><td>0</td><td>0.49</td><td>0</td><td>&quot;申达股份证券买入&quot;</td></tr><tr><td>&quot;600178&quot;</td><td>&quot;东安动力&quot;</td><td>2023-05-24</td><td>09:59:17</td><td>16400</td><td>6.07</td><td>99548.0</td><td>-99558.97</td><td>&quot;买&quot;</td><td>9.95</td><td>0</td><td>1.02</td><td>0</td><td>&quot;东安动力证券买入&quot;</td></tr><tr><td>&quot;603002&quot;</td><td>&quot;宏昌电子&quot;</td><td>2023-05-24</td><td>09:54:48</td><td>9800</td><td>5.06</td><td>49588.0</td><td>-49593.46</td><td>&quot;买&quot;</td><td>4.96</td><td>0</td><td>0.5</td><td>0</td><td>&quot;宏昌电子证券买入&quot;</td></tr><tr><td>&quot;300107&quot;</td><td>&quot;建新股份&quot;</td><td>2023-05-23</td><td>10:01:57</td><td>10000</td><td>5.0</td><td>50000.0</td><td>-50005.0</td><td>&quot;买&quot;</td><td>5.0</td><td>0</td><td>0.0</td><td>0</td><td>&quot;建新股份证券买入&quot;</td></tr><tr><td>&quot;002224&quot;</td><td>&quot;三 力 士&quot;</td><td>2023-05-23</td><td>09:58:07</td><td>10800</td><td>4.59</td><td>49572.0</td><td>-49576.96</td><td>&quot;买&quot;</td><td>4.96</td><td>0</td><td>0.0</td><td>0</td><td>&quot;三 力 士证券买入&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 14)\n",
       "┌──────────┬──────────┬────────────┬──────────┬───┬────────┬────────┬────────┬──────────────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ 成交日期   ┆ 成交时间 ┆ … ┆ 印花税 ┆ 过户费 ┆ 其他费 ┆ 备注             │\n",
       "│ ---      ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---    ┆ ---              │\n",
       "│ str      ┆ str      ┆ date       ┆ time     ┆   ┆ i64    ┆ f64    ┆ i64    ┆ str              │\n",
       "╞══════════╪══════════╪════════════╪══════════╪═══╪════════╪════════╪════════╪══════════════════╡\n",
       "│ 600626   ┆ 申达股份 ┆ 2023-05-24 ┆ 10:06:14 ┆ … ┆ 0      ┆ 0.49   ┆ 0      ┆ 申达股份证券买入 │\n",
       "│ 600178   ┆ 东安动力 ┆ 2023-05-24 ┆ 09:59:17 ┆ … ┆ 0      ┆ 1.02   ┆ 0      ┆ 东安动力证券买入 │\n",
       "│ 603002   ┆ 宏昌电子 ┆ 2023-05-24 ┆ 09:54:48 ┆ … ┆ 0      ┆ 0.5    ┆ 0      ┆ 宏昌电子证券买入 │\n",
       "│ 300107   ┆ 建新股份 ┆ 2023-05-23 ┆ 10:01:57 ┆ … ┆ 0      ┆ 0.0    ┆ 0      ┆ 建新股份证券买入 │\n",
       "│ 002224   ┆ 三 力 士 ┆ 2023-05-23 ┆ 09:58:07 ┆ … ┆ 0      ┆ 0.0    ┆ 0      ┆ 三 力 士证券买入 │\n",
       "└──────────┴──────────┴────────────┴──────────┴───┴────────┴────────┴────────┴──────────────────┘"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pl.read_excel(\n",
    "    \"stock_trades/202305-海通普通.xlsx\",\n",
    "    schema_overrides={\n",
    "        \"成交日期\": pl.String,\n",
    "        \"成交时间\": pl.String,\n",
    "    },\n",
    ")\n",
    "df.filter(pl.col(\"成交时间\") != \"\").filter(pl.col(\"操作\").is_in([\"买\", \"卖\"])).filter(\n",
    "    (~pl.col(\"证券代码\").str.starts_with(\"204\"))\n",
    "    & (~pl.col(\"证券代码\").str.starts_with(\"1318\"))\n",
    ").with_columns(\n",
    "    pl.col(\"成交日期\").str.to_date(\"%Y%m%d\"),\n",
    "    pl.col(\"成交时间\").str.to_time(\"%H:%M:%S\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "2aff7fa0-81db-4423-9322-4fbe15ccb09d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_df_海通普通(f: str | Path) -> pl.DataFrame:\n",
    "    df = pl.read_excel(\n",
    "        f,\n",
    "        schema_overrides={\n",
    "            \"成交日期\": pl.String,\n",
    "            \"成交时间\": pl.String,\n",
    "            \"成交数量\": pl.Float64,\n",
    "            \"成交金额\": pl.Float64,\n",
    "            \"印花税\": pl.Float64,\n",
    "            \"其他费\": pl.Float64,\n",
    "        },\n",
    "    )\n",
    "    df = df.filter(\n",
    "        (pl.col(\"成交时间\") != \"\")\n",
    "        & (pl.col(\"操作\").is_in([\"买\", \"卖\"]))\n",
    "        & (~pl.col(\"证券代码\").str.starts_with(\"204\"))\n",
    "        & (~pl.col(\"证券代码\").str.starts_with(\"1318\"))\n",
    "    ).with_columns(\n",
    "        pl.col(\"成交日期\").str.to_date(\"%Y%m%d\"),\n",
    "        pl.col(\"成交时间\").str.to_time(\"%H:%M:%S\"),\n",
    "    )\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "85747675-bb75-4c69-b91a-a540614f7130",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (53, 15)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>成交日期</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>操作</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th><th>券商</th></tr><tr><td>str</td><td>str</td><td>date</td><td>time</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;002224&quot;</td><td>&quot;三 力 士&quot;</td><td>2023-05-23</td><td>09:58:07</td><td>10800.0</td><td>4.59</td><td>49572.0</td><td>-49576.96</td><td>&quot;买&quot;</td><td>4.96</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;三 力 士证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;300107&quot;</td><td>&quot;建新股份&quot;</td><td>2023-05-23</td><td>10:01:57</td><td>10000.0</td><td>5.0</td><td>50000.0</td><td>-50005.0</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;建新股份证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;603002&quot;</td><td>&quot;宏昌电子&quot;</td><td>2023-05-24</td><td>09:54:48</td><td>9800.0</td><td>5.06</td><td>49588.0</td><td>-49593.46</td><td>&quot;买&quot;</td><td>4.96</td><td>0.0</td><td>0.5</td><td>0.0</td><td>&quot;宏昌电子证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;600178&quot;</td><td>&quot;东安动力&quot;</td><td>2023-05-24</td><td>09:59:17</td><td>16400.0</td><td>6.07</td><td>99548.0</td><td>-99558.97</td><td>&quot;买&quot;</td><td>9.95</td><td>0.0</td><td>1.02</td><td>0.0</td><td>&quot;东安动力证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;600626&quot;</td><td>&quot;申达股份&quot;</td><td>2023-05-24</td><td>10:06:14</td><td>14800.0</td><td>3.37</td><td>49876.0</td><td>-49881.48</td><td>&quot;买&quot;</td><td>4.99</td><td>0.0</td><td>0.49</td><td>0.0</td><td>&quot;申达股份证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;603386&quot;</td><td>&quot;骏亚科技&quot;</td><td>2023-07-24</td><td>09:38:22</td><td>4500.0</td><td>11.32</td><td>50940.0</td><td>50883.51</td><td>&quot;卖&quot;</td><td>5.09</td><td>50.91</td><td>0.49</td><td>0.0</td><td>&quot;骏亚科技证券卖出&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;002225&quot;</td><td>&quot;濮耐股份&quot;</td><td>2023-07-24</td><td>09:42:11</td><td>13200.0</td><td>3.8</td><td>50160.0</td><td>-50165.02</td><td>&quot;买&quot;</td><td>5.02</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;濮耐股份证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;600735&quot;</td><td>&quot;新华锦&quot;</td><td>2023-07-24</td><td>09:48:27</td><td>8200.0</td><td>6.07</td><td>49774.0</td><td>-49779.48</td><td>&quot;买&quot;</td><td>4.98</td><td>0.0</td><td>0.5</td><td>0.0</td><td>&quot;新华锦证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;300642&quot;</td><td>&quot;透景生命&quot;</td><td>2023-07-24</td><td>09:49:52</td><td>2500.0</td><td>19.69</td><td>49225.0</td><td>-49229.92</td><td>&quot;买&quot;</td><td>4.92</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;透景生命证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;000823&quot;</td><td>&quot;超声电子&quot;</td><td>2023-07-24</td><td>09:52:23</td><td>5400.0</td><td>9.38</td><td>50652.0</td><td>-50657.07</td><td>&quot;买&quot;</td><td>5.07</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;超声电子证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (53, 15)\n",
       "┌──────────┬──────────┬────────────┬──────────┬───┬────────┬────────┬──────────────────┬──────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ 成交日期   ┆ 成交时间 ┆ … ┆ 过户费 ┆ 其他费 ┆ 备注             ┆ 券商     │\n",
       "│ ---      ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---              ┆ ---      │\n",
       "│ str      ┆ str      ┆ date       ┆ time     ┆   ┆ f64    ┆ f64    ┆ str              ┆ str      │\n",
       "╞══════════╪══════════╪════════════╪══════════╪═══╪════════╪════════╪══════════════════╪══════════╡\n",
       "│ 002224   ┆ 三 力 士 ┆ 2023-05-23 ┆ 09:58:07 ┆ … ┆ 0.0    ┆ 0.0    ┆ 三 力 士证券买入 ┆ 海通普通 │\n",
       "│ 300107   ┆ 建新股份 ┆ 2023-05-23 ┆ 10:01:57 ┆ … ┆ 0.0    ┆ 0.0    ┆ 建新股份证券买入 ┆ 海通普通 │\n",
       "│ 603002   ┆ 宏昌电子 ┆ 2023-05-24 ┆ 09:54:48 ┆ … ┆ 0.5    ┆ 0.0    ┆ 宏昌电子证券买入 ┆ 海通普通 │\n",
       "│ 600178   ┆ 东安动力 ┆ 2023-05-24 ┆ 09:59:17 ┆ … ┆ 1.02   ┆ 0.0    ┆ 东安动力证券买入 ┆ 海通普通 │\n",
       "│ 600626   ┆ 申达股份 ┆ 2023-05-24 ┆ 10:06:14 ┆ … ┆ 0.49   ┆ 0.0    ┆ 申达股份证券买入 ┆ 海通普通 │\n",
       "│ …        ┆ …        ┆ …          ┆ …        ┆ … ┆ …      ┆ …      ┆ …                ┆ …        │\n",
       "│ 603386   ┆ 骏亚科技 ┆ 2023-07-24 ┆ 09:38:22 ┆ … ┆ 0.49   ┆ 0.0    ┆ 骏亚科技证券卖出 ┆ 海通普通 │\n",
       "│ 002225   ┆ 濮耐股份 ┆ 2023-07-24 ┆ 09:42:11 ┆ … ┆ 0.0    ┆ 0.0    ┆ 濮耐股份证券买入 ┆ 海通普通 │\n",
       "│ 600735   ┆ 新华锦   ┆ 2023-07-24 ┆ 09:48:27 ┆ … ┆ 0.5    ┆ 0.0    ┆ 新华锦证券买入   ┆ 海通普通 │\n",
       "│ 300642   ┆ 透景生命 ┆ 2023-07-24 ┆ 09:49:52 ┆ … ┆ 0.0    ┆ 0.0    ┆ 透景生命证券买入 ┆ 海通普通 │\n",
       "│ 000823   ┆ 超声电子 ┆ 2023-07-24 ┆ 09:52:23 ┆ … ┆ 0.0    ┆ 0.0    ┆ 超声电子证券买入 ┆ 海通普通 │\n",
       "└──────────┴──────────┴────────────┴──────────┴───┴────────┴────────┴──────────────────┴──────────┘"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = [read_df_海通普通(p) for p in Path(\"stock_trades/\").glob(\"*-海通普通.xlsx\")]\n",
    "df = pl.concat(df)\n",
    "d2 = df.with_columns(券商=pl.lit(\"海通普通\"))\n",
    "d2.sort(\"成交日期\", \"成交时间\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "2b61583a-48d4-496a-ae51-828887625b7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (53, 15)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>成交日期</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>操作</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th><th>券商</th></tr><tr><td>str</td><td>str</td><td>date</td><td>time</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;600638&quot;</td><td>&quot;新黄浦&quot;</td><td>2023-07-26</td><td>09:33:56</td><td>9300.0</td><td>6.526</td><td>60696.0</td><td>60628.01</td><td>&quot;卖&quot;</td><td>6.68</td><td>60.7</td><td>0.61</td><td>0.0</td><td>&quot;新黄浦证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>2023-07-27</td><td>09:30:42</td><td>8700.0</td><td>6.12</td><td>53244.0</td><td>53184.91</td><td>&quot;卖&quot;</td><td>5.86</td><td>53.23</td><td>0.0</td><td>0.0</td><td>&quot;恒基达鑫证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002136&quot;</td><td>&quot;安 纳 达&quot;</td><td>2023-07-27</td><td>09:32:52</td><td>4400.0</td><td>11.881</td><td>52277.0</td><td>52218.97</td><td>&quot;卖&quot;</td><td>5.75</td><td>52.28</td><td>0.0</td><td>0.0</td><td>&quot;安 纳 达证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;600300&quot;</td><td>&quot;维维股份&quot;</td><td>2023-08-04</td><td>09:36:55</td><td>16400.0</td><td>3.27</td><td>53628.0</td><td>53567.91</td><td>&quot;卖&quot;</td><td>5.9</td><td>53.65</td><td>0.54</td><td>0.0</td><td>&quot;维维股份证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;300241&quot;</td><td>&quot;瑞丰光电&quot;</td><td>2023-08-04</td><td>09:39:38</td><td>9700.0</td><td>5.13</td><td>49761.0</td><td>-49766.47</td><td>&quot;买&quot;</td><td>5.47</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;瑞丰光电证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>2023-10-31</td><td>09:31:53</td><td>5000.0</td><td>14.13</td><td>70650.0</td><td>70607.91</td><td>&quot;卖&quot;</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>&quot;西麦食品证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>2023-10-31</td><td>09:39:57</td><td>3100.0</td><td>15.84</td><td>49104.0</td><td>-49109.51</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>&quot;爱婴室证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>2023-10-31</td><td>09:40:55</td><td>9600.0</td><td>5.21</td><td>50016.0</td><td>-50021.0</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;青松股份证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>2023-10-31</td><td>09:43:13</td><td>8400.0</td><td>5.91</td><td>49644.0</td><td>-49649.0</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;恒基达鑫证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>2023-10-31</td><td>09:44:45</td><td>5400.0</td><td>9.24</td><td>49896.0</td><td>-49901.0</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;威海广泰证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (53, 15)\n",
       "┌──────────┬──────────┬────────────┬──────────┬───┬────────┬────────┬──────────────────┬──────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ 成交日期   ┆ 成交时间 ┆ … ┆ 过户费 ┆ 其他费 ┆ 备注             ┆ 券商     │\n",
       "│ ---      ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---              ┆ ---      │\n",
       "│ str      ┆ str      ┆ date       ┆ time     ┆   ┆ f64    ┆ f64    ┆ str              ┆ str      │\n",
       "╞══════════╪══════════╪════════════╪══════════╪═══╪════════╪════════╪══════════════════╪══════════╡\n",
       "│ 600638   ┆ 新黄浦   ┆ 2023-07-26 ┆ 09:33:56 ┆ … ┆ 0.61   ┆ 0.0    ┆ 新黄浦证券卖出   ┆ 海通两融 │\n",
       "│ 002492   ┆ 恒基达鑫 ┆ 2023-07-27 ┆ 09:30:42 ┆ … ┆ 0.0    ┆ 0.0    ┆ 恒基达鑫证券卖出 ┆ 海通两融 │\n",
       "│ 002136   ┆ 安 纳 达 ┆ 2023-07-27 ┆ 09:32:52 ┆ … ┆ 0.0    ┆ 0.0    ┆ 安 纳 达证券卖出 ┆ 海通两融 │\n",
       "│ 600300   ┆ 维维股份 ┆ 2023-08-04 ┆ 09:36:55 ┆ … ┆ 0.54   ┆ 0.0    ┆ 维维股份证券卖出 ┆ 海通两融 │\n",
       "│ 300241   ┆ 瑞丰光电 ┆ 2023-08-04 ┆ 09:39:38 ┆ … ┆ 0.0    ┆ 0.0    ┆ 瑞丰光电证券买入 ┆ 海通两融 │\n",
       "│ …        ┆ …        ┆ …          ┆ …        ┆ … ┆ …      ┆ …      ┆ …                ┆ …        │\n",
       "│ 002956   ┆ 西麦食品 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 0.0    ┆ 0.0    ┆ 西麦食品证券卖出 ┆ 海通两融 │\n",
       "│ 603214   ┆ 爱婴室   ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ 0.51   ┆ 0.0    ┆ 爱婴室证券买入   ┆ 海通两融 │\n",
       "│ 300132   ┆ 青松股份 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ 0.0    ┆ 0.0    ┆ 青松股份证券买入 ┆ 海通两融 │\n",
       "│ 002492   ┆ 恒基达鑫 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ 0.0    ┆ 0.0    ┆ 恒基达鑫证券买入 ┆ 海通两融 │\n",
       "│ 002111   ┆ 威海广泰 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ 0.0    ┆ 0.0    ┆ 威海广泰证券买入 ┆ 海通两融 │\n",
       "└──────────┴──────────┴────────────┴──────────┴───┴────────┴────────┴──────────────────┴──────────┘"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = [read_df_海通普通(p) for p in Path(\"stock_trades/\").glob(\"*-海通两融.xlsx\")]\n",
    "df = pl.concat(df)\n",
    "d3 = df.with_columns(券商=pl.lit(\"海通两融\"))\n",
    "d3.sort(\"成交日期\", \"成交时间\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "0d338dff-34ab-46dd-9618-c6659305d2b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = d1.select(\n",
    "    券商=pl.col(\"券商\"),\n",
    "    交易日期=pl.col(\"发生日期\"),\n",
    "    交易时间=pl.col(\"成交时间\"),\n",
    "    证券代码=pl.col(\"证券代码\"),\n",
    "    证券名称=pl.col(\"证券名称\"),\n",
    "    买卖标志=pl.col(\"业务名称\").replace({\"证券卖出\": \"卖出\", \"证券买入\": \"买入\"}),\n",
    "    成交价格=pl.col(\"成交价格\"),\n",
    "    成交数量=pl.col(\"成交数量\").abs(),\n",
    "    成交金额=pl.col(\"成交金额\"),\n",
    "    手续费=pl.col(\"手续费\"),\n",
    "    印花税=pl.col(\"印花税\"),\n",
    "    过户费=pl.col(\"过户费\"),\n",
    "    其他费=pl.col(\"其他费\"),\n",
    "    发生金额=pl.col(\"发生金额\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "5400655d-4319-4335-84d8-73d8d3dbf2bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (53, 15)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>成交日期</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>操作</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th><th>券商</th></tr><tr><td>str</td><td>str</td><td>date</td><td>time</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;600626&quot;</td><td>&quot;申达股份&quot;</td><td>2023-05-24</td><td>10:06:14</td><td>14800.0</td><td>3.37</td><td>49876.0</td><td>-49881.48</td><td>&quot;买&quot;</td><td>4.99</td><td>0.0</td><td>0.49</td><td>0.0</td><td>&quot;申达股份证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;600178&quot;</td><td>&quot;东安动力&quot;</td><td>2023-05-24</td><td>09:59:17</td><td>16400.0</td><td>6.07</td><td>99548.0</td><td>-99558.97</td><td>&quot;买&quot;</td><td>9.95</td><td>0.0</td><td>1.02</td><td>0.0</td><td>&quot;东安动力证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;603002&quot;</td><td>&quot;宏昌电子&quot;</td><td>2023-05-24</td><td>09:54:48</td><td>9800.0</td><td>5.06</td><td>49588.0</td><td>-49593.46</td><td>&quot;买&quot;</td><td>4.96</td><td>0.0</td><td>0.5</td><td>0.0</td><td>&quot;宏昌电子证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;300107&quot;</td><td>&quot;建新股份&quot;</td><td>2023-05-23</td><td>10:01:57</td><td>10000.0</td><td>5.0</td><td>50000.0</td><td>-50005.0</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;建新股份证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;002224&quot;</td><td>&quot;三 力 士&quot;</td><td>2023-05-23</td><td>09:58:07</td><td>10800.0</td><td>4.59</td><td>49572.0</td><td>-49576.96</td><td>&quot;买&quot;</td><td>4.96</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;三 力 士证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;605196&quot;</td><td>&quot;华通线缆&quot;</td><td>2023-07-17</td><td>10:03:09</td><td>7200.0</td><td>7.81</td><td>56232.0</td><td>56169.59</td><td>&quot;卖&quot;</td><td>5.62</td><td>56.23</td><td>0.56</td><td>0.0</td><td>&quot;华通线缆证券卖出&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;002842&quot;</td><td>&quot;翔鹭钨业&quot;</td><td>2023-07-11</td><td>10:04:39</td><td>5900.0</td><td>8.68</td><td>51212.0</td><td>51155.66</td><td>&quot;卖&quot;</td><td>5.12</td><td>51.22</td><td>0.0</td><td>0.0</td><td>&quot;翔鹭钨业证券卖出&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;002331&quot;</td><td>&quot;皖通科技&quot;</td><td>2023-07-11</td><td>10:03:52</td><td>6900.0</td><td>7.33</td><td>50577.0</td><td>50521.36</td><td>&quot;卖&quot;</td><td>5.06</td><td>50.58</td><td>0.0</td><td>0.0</td><td>&quot;皖通科技证券卖出&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;600300&quot;</td><td>&quot;维维股份&quot;</td><td>2023-07-11</td><td>10:13:04</td><td>16400.0</td><td>3.05</td><td>50020.0</td><td>-50025.5</td><td>&quot;买&quot;</td><td>5.0</td><td>0.0</td><td>0.5</td><td>0.0</td><td>&quot;维维股份证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr><tr><td>&quot;600287&quot;</td><td>&quot;江苏舜天&quot;</td><td>2023-07-11</td><td>10:08:50</td><td>9900.0</td><td>5.06</td><td>50094.0</td><td>-50099.51</td><td>&quot;买&quot;</td><td>5.01</td><td>0.0</td><td>0.5</td><td>0.0</td><td>&quot;江苏舜天证券买入&quot;</td><td>&quot;海通普通&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (53, 15)\n",
       "┌──────────┬──────────┬────────────┬──────────┬───┬────────┬────────┬──────────────────┬──────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ 成交日期   ┆ 成交时间 ┆ … ┆ 过户费 ┆ 其他费 ┆ 备注             ┆ 券商     │\n",
       "│ ---      ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---              ┆ ---      │\n",
       "│ str      ┆ str      ┆ date       ┆ time     ┆   ┆ f64    ┆ f64    ┆ str              ┆ str      │\n",
       "╞══════════╪══════════╪════════════╪══════════╪═══╪════════╪════════╪══════════════════╪══════════╡\n",
       "│ 600626   ┆ 申达股份 ┆ 2023-05-24 ┆ 10:06:14 ┆ … ┆ 0.49   ┆ 0.0    ┆ 申达股份证券买入 ┆ 海通普通 │\n",
       "│ 600178   ┆ 东安动力 ┆ 2023-05-24 ┆ 09:59:17 ┆ … ┆ 1.02   ┆ 0.0    ┆ 东安动力证券买入 ┆ 海通普通 │\n",
       "│ 603002   ┆ 宏昌电子 ┆ 2023-05-24 ┆ 09:54:48 ┆ … ┆ 0.5    ┆ 0.0    ┆ 宏昌电子证券买入 ┆ 海通普通 │\n",
       "│ 300107   ┆ 建新股份 ┆ 2023-05-23 ┆ 10:01:57 ┆ … ┆ 0.0    ┆ 0.0    ┆ 建新股份证券买入 ┆ 海通普通 │\n",
       "│ 002224   ┆ 三 力 士 ┆ 2023-05-23 ┆ 09:58:07 ┆ … ┆ 0.0    ┆ 0.0    ┆ 三 力 士证券买入 ┆ 海通普通 │\n",
       "│ …        ┆ …        ┆ …          ┆ …        ┆ … ┆ …      ┆ …      ┆ …                ┆ …        │\n",
       "│ 605196   ┆ 华通线缆 ┆ 2023-07-17 ┆ 10:03:09 ┆ … ┆ 0.56   ┆ 0.0    ┆ 华通线缆证券卖出 ┆ 海通普通 │\n",
       "│ 002842   ┆ 翔鹭钨业 ┆ 2023-07-11 ┆ 10:04:39 ┆ … ┆ 0.0    ┆ 0.0    ┆ 翔鹭钨业证券卖出 ┆ 海通普通 │\n",
       "│ 002331   ┆ 皖通科技 ┆ 2023-07-11 ┆ 10:03:52 ┆ … ┆ 0.0    ┆ 0.0    ┆ 皖通科技证券卖出 ┆ 海通普通 │\n",
       "│ 600300   ┆ 维维股份 ┆ 2023-07-11 ┆ 10:13:04 ┆ … ┆ 0.5    ┆ 0.0    ┆ 维维股份证券买入 ┆ 海通普通 │\n",
       "│ 600287   ┆ 江苏舜天 ┆ 2023-07-11 ┆ 10:08:50 ┆ … ┆ 0.5    ┆ 0.0    ┆ 江苏舜天证券买入 ┆ 海通普通 │\n",
       "└──────────┴──────────┴────────────┴──────────┴───┴────────┴────────┴──────────────────┴──────────┘"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "81cb282c-3f7f-440a-b5b1-173bf580d5eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "d2 = d2.select(\n",
    "    券商=pl.col(\"券商\"),\n",
    "    交易日期=pl.col(\"成交日期\"),\n",
    "    交易时间=pl.col(\"成交时间\"),\n",
    "    证券代码=pl.col(\"证券代码\"),\n",
    "    证券名称=pl.col(\"证券名称\"),\n",
    "    买卖标志=pl.col(\"操作\").replace({\"卖\": \"卖出\", \"买\": \"买入\"}),\n",
    "    成交价格=pl.col(\"成交价格\"),\n",
    "    成交数量=pl.col(\"成交数量\").abs(),\n",
    "    成交金额=pl.col(\"成交金额\"),\n",
    "    手续费=pl.col(\"手续费\"),\n",
    "    印花税=pl.col(\"印花税\"),\n",
    "    过户费=pl.col(\"过户费\"),\n",
    "    其他费=pl.col(\"其他费\"),\n",
    "    发生金额=pl.col(\"发生金额\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "6a30bae8-1971-416d-a1cf-28dccc2df2d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (53, 15)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>成交日期</th><th>成交时间</th><th>成交数量</th><th>成交价格</th><th>成交金额</th><th>发生金额</th><th>操作</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>备注</th><th>券商</th></tr><tr><td>str</td><td>str</td><td>date</td><td>time</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;600638&quot;</td><td>&quot;新黄浦&quot;</td><td>2023-07-26</td><td>09:33:56</td><td>9300.0</td><td>6.526</td><td>60696.0</td><td>60628.01</td><td>&quot;卖&quot;</td><td>6.68</td><td>60.7</td><td>0.61</td><td>0.0</td><td>&quot;新黄浦证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>2023-07-27</td><td>09:30:42</td><td>8700.0</td><td>6.12</td><td>53244.0</td><td>53184.91</td><td>&quot;卖&quot;</td><td>5.86</td><td>53.23</td><td>0.0</td><td>0.0</td><td>&quot;恒基达鑫证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002136&quot;</td><td>&quot;安 纳 达&quot;</td><td>2023-07-27</td><td>09:32:52</td><td>4400.0</td><td>11.881</td><td>52277.0</td><td>52218.97</td><td>&quot;卖&quot;</td><td>5.75</td><td>52.28</td><td>0.0</td><td>0.0</td><td>&quot;安 纳 达证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>2023-08-22</td><td>09:36:13</td><td>10600.0</td><td>9.36</td><td>99216.0</td><td>99104.85</td><td>&quot;卖&quot;</td><td>10.91</td><td>99.24</td><td>1.0</td><td>0.0</td><td>&quot;中创物流证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>2023-08-04</td><td>10:07:37</td><td>10600.0</td><td>9.43</td><td>99958.0</td><td>-99970.01</td><td>&quot;买&quot;</td><td>11.0</td><td>0.0</td><td>1.01</td><td>0.0</td><td>&quot;中创物流证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;300464&quot;</td><td>&quot;星徽股份&quot;</td><td>2023-10-18</td><td>09:46:15</td><td>16100.0</td><td>5.74</td><td>92414.0</td><td>92358.97</td><td>&quot;卖&quot;</td><td>8.82</td><td>46.21</td><td>0.0</td><td>0.0</td><td>&quot;星徽股份证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002661&quot;</td><td>&quot;克明食品&quot;</td><td>2023-10-18</td><td>09:55:41</td><td>8500.0</td><td>9.42</td><td>80072.0</td><td>-80079.64</td><td>&quot;买&quot;</td><td>7.64</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;克明食品证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;002753&quot;</td><td>&quot;永东股份&quot;</td><td>2023-10-09</td><td>09:48:02</td><td>14200.0</td><td>7.02</td><td>99684.0</td><td>-99693.51</td><td>&quot;买&quot;</td><td>9.51</td><td>0.0</td><td>0.0</td><td>0.0</td><td>&quot;永东股份证券买入&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;000698&quot;</td><td>&quot;沈阳化工&quot;</td><td>2023-10-09</td><td>09:45:18</td><td>23800.0</td><td>4.053</td><td>96460.0</td><td>96402.56</td><td>&quot;卖&quot;</td><td>9.2</td><td>48.24</td><td>0.0</td><td>0.0</td><td>&quot;沈阳化工证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr><tr><td>&quot;605288&quot;</td><td>&quot;凯迪股份&quot;</td><td>2023-10-09</td><td>09:44:40</td><td>2500.0</td><td>40.838</td><td>102094.0</td><td>102032.21</td><td>&quot;卖&quot;</td><td>9.74</td><td>51.05</td><td>1.0</td><td>0.0</td><td>&quot;凯迪股份证券卖出&quot;</td><td>&quot;海通两融&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (53, 15)\n",
       "┌──────────┬──────────┬────────────┬──────────┬───┬────────┬────────┬──────────────────┬──────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ 成交日期   ┆ 成交时间 ┆ … ┆ 过户费 ┆ 其他费 ┆ 备注             ┆ 券商     │\n",
       "│ ---      ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---              ┆ ---      │\n",
       "│ str      ┆ str      ┆ date       ┆ time     ┆   ┆ f64    ┆ f64    ┆ str              ┆ str      │\n",
       "╞══════════╪══════════╪════════════╪══════════╪═══╪════════╪════════╪══════════════════╪══════════╡\n",
       "│ 600638   ┆ 新黄浦   ┆ 2023-07-26 ┆ 09:33:56 ┆ … ┆ 0.61   ┆ 0.0    ┆ 新黄浦证券卖出   ┆ 海通两融 │\n",
       "│ 002492   ┆ 恒基达鑫 ┆ 2023-07-27 ┆ 09:30:42 ┆ … ┆ 0.0    ┆ 0.0    ┆ 恒基达鑫证券卖出 ┆ 海通两融 │\n",
       "│ 002136   ┆ 安 纳 达 ┆ 2023-07-27 ┆ 09:32:52 ┆ … ┆ 0.0    ┆ 0.0    ┆ 安 纳 达证券卖出 ┆ 海通两融 │\n",
       "│ 603967   ┆ 中创物流 ┆ 2023-08-22 ┆ 09:36:13 ┆ … ┆ 1.0    ┆ 0.0    ┆ 中创物流证券卖出 ┆ 海通两融 │\n",
       "│ 603967   ┆ 中创物流 ┆ 2023-08-04 ┆ 10:07:37 ┆ … ┆ 1.01   ┆ 0.0    ┆ 中创物流证券买入 ┆ 海通两融 │\n",
       "│ …        ┆ …        ┆ …          ┆ …        ┆ … ┆ …      ┆ …      ┆ …                ┆ …        │\n",
       "│ 300464   ┆ 星徽股份 ┆ 2023-10-18 ┆ 09:46:15 ┆ … ┆ 0.0    ┆ 0.0    ┆ 星徽股份证券卖出 ┆ 海通两融 │\n",
       "│ 002661   ┆ 克明食品 ┆ 2023-10-18 ┆ 09:55:41 ┆ … ┆ 0.0    ┆ 0.0    ┆ 克明食品证券买入 ┆ 海通两融 │\n",
       "│ 002753   ┆ 永东股份 ┆ 2023-10-09 ┆ 09:48:02 ┆ … ┆ 0.0    ┆ 0.0    ┆ 永东股份证券买入 ┆ 海通两融 │\n",
       "│ 000698   ┆ 沈阳化工 ┆ 2023-10-09 ┆ 09:45:18 ┆ … ┆ 0.0    ┆ 0.0    ┆ 沈阳化工证券卖出 ┆ 海通两融 │\n",
       "│ 605288   ┆ 凯迪股份 ┆ 2023-10-09 ┆ 09:44:40 ┆ … ┆ 1.0    ┆ 0.0    ┆ 凯迪股份证券卖出 ┆ 海通两融 │\n",
       "└──────────┴──────────┴────────────┴──────────┴───┴────────┴────────┴──────────────────┴──────────┘"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "a543d57f-bf89-43d8-93b5-371d556e5afa",
   "metadata": {},
   "outputs": [],
   "source": [
    "d3 = d3.select(\n",
    "    券商=pl.col(\"券商\"),\n",
    "    交易日期=pl.col(\"成交日期\"),\n",
    "    交易时间=pl.col(\"成交时间\"),\n",
    "    证券代码=pl.col(\"证券代码\"),\n",
    "    证券名称=pl.col(\"证券名称\"),\n",
    "    买卖标志=pl.col(\"操作\").replace({\"卖\": \"卖出\", \"买\": \"买入\"}),\n",
    "    成交价格=pl.col(\"成交价格\"),\n",
    "    成交数量=pl.col(\"成交数量\").abs(),\n",
    "    成交金额=pl.col(\"成交金额\"),\n",
    "    手续费=pl.col(\"手续费\"),\n",
    "    印花税=pl.col(\"印花税\"),\n",
    "    过户费=pl.col(\"过户费\"),\n",
    "    其他费=pl.col(\"其他费\"),\n",
    "    发生金额=pl.col(\"发生金额\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "d83a5e10-ce5a-459c-a2d2-270d45c8d1c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pl.concat([d1, d2, d3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "29a9ac34-0e30-48f5-8514-7f6ba2e2b934",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 14)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th></tr><tr><td>str</td><td>date</td><td>time</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:38:10</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;卖出&quot;</td><td>13.2062</td><td>10400.0</td><td>137344.0</td><td>21.98</td><td>137.35</td><td>1.38</td><td>0.0</td><td>137184.67</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:44:52</td><td>&quot;600408&quot;</td><td>&quot;安泰集团&quot;</td><td>&quot;买入&quot;</td><td>3.19</td><td>47000.0</td><td>149930.0</td><td>23.99</td><td>0.0</td><td>1.51</td><td>0.0</td><td>-149955.5</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:44:31</td><td>&quot;600648&quot;</td><td>&quot;外高桥&quot;</td><td>&quot;买入&quot;</td><td>12.6066</td><td>11900.0</td><td>150019.0</td><td>24.0</td><td>0.0</td><td>1.49</td><td>0.0</td><td>-150044.49</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:43:38</td><td>&quot;600269&quot;</td><td>&quot;赣粤高速&quot;</td><td>&quot;买入&quot;</td><td>3.69</td><td>40700.0</td><td>150183.0</td><td>24.03</td><td>0.0</td><td>1.5</td><td>0.0</td><td>-150208.53</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:42:51</td><td>&quot;600015&quot;</td><td>&quot;华夏银行&quot;</td><td>&quot;买入&quot;</td><td>5.07</td><td>30000.0</td><td>152100.0</td><td>24.34</td><td>0.0</td><td>1.52</td><td>0.0</td><td>-152125.86</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-18</td><td>09:46:15</td><td>&quot;300464&quot;</td><td>&quot;星徽股份&quot;</td><td>&quot;卖出&quot;</td><td>5.74</td><td>16100.0</td><td>92414.0</td><td>8.82</td><td>46.21</td><td>0.0</td><td>0.0</td><td>92358.97</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-18</td><td>09:55:41</td><td>&quot;002661&quot;</td><td>&quot;克明食品&quot;</td><td>&quot;买入&quot;</td><td>9.42</td><td>8500.0</td><td>80072.0</td><td>7.64</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-80079.64</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-09</td><td>09:48:02</td><td>&quot;002753&quot;</td><td>&quot;永东股份&quot;</td><td>&quot;买入&quot;</td><td>7.02</td><td>14200.0</td><td>99684.0</td><td>9.51</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-99693.51</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-09</td><td>09:45:18</td><td>&quot;000698&quot;</td><td>&quot;沈阳化工&quot;</td><td>&quot;卖出&quot;</td><td>4.053</td><td>23800.0</td><td>96460.0</td><td>9.2</td><td>48.24</td><td>0.0</td><td>0.0</td><td>96402.56</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-09</td><td>09:44:40</td><td>&quot;605288&quot;</td><td>&quot;凯迪股份&quot;</td><td>&quot;卖出&quot;</td><td>40.838</td><td>2500.0</td><td>102094.0</td><td>9.74</td><td>51.05</td><td>1.0</td><td>0.0</td><td>102032.21</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 14)\n",
       "┌──────────┬────────────┬──────────┬──────────┬───┬────────┬────────┬────────┬────────────┐\n",
       "│ 券商     ┆ 交易日期   ┆ 交易时间 ┆ 证券代码 ┆ … ┆ 印花税 ┆ 过户费 ┆ 其他费 ┆ 发生金额   │\n",
       "│ ---      ┆ ---        ┆ ---      ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---    ┆ ---        │\n",
       "│ str      ┆ date       ┆ time     ┆ str      ┆   ┆ f64    ┆ f64    ┆ f64    ┆ f64        │\n",
       "╞══════════╪════════════╪══════════╪══════════╪═══╪════════╪════════╪════════╪════════════╡\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:38:10 ┆ 002462   ┆ … ┆ 137.35 ┆ 1.38   ┆ 0.0    ┆ 137184.67  │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:44:52 ┆ 600408   ┆ … ┆ 0.0    ┆ 1.51   ┆ 0.0    ┆ -149955.5  │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:44:31 ┆ 600648   ┆ … ┆ 0.0    ┆ 1.49   ┆ 0.0    ┆ -150044.49 │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:43:38 ┆ 600269   ┆ … ┆ 0.0    ┆ 1.5    ┆ 0.0    ┆ -150208.53 │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:42:51 ┆ 600015   ┆ … ┆ 0.0    ┆ 1.52   ┆ 0.0    ┆ -152125.86 │\n",
       "│ …        ┆ …          ┆ …        ┆ …        ┆ … ┆ …      ┆ …      ┆ …      ┆ …          │\n",
       "│ 海通两融 ┆ 2023-10-18 ┆ 09:46:15 ┆ 300464   ┆ … ┆ 46.21  ┆ 0.0    ┆ 0.0    ┆ 92358.97   │\n",
       "│ 海通两融 ┆ 2023-10-18 ┆ 09:55:41 ┆ 002661   ┆ … ┆ 0.0    ┆ 0.0    ┆ 0.0    ┆ -80079.64  │\n",
       "│ 海通两融 ┆ 2023-10-09 ┆ 09:48:02 ┆ 002753   ┆ … ┆ 0.0    ┆ 0.0    ┆ 0.0    ┆ -99693.51  │\n",
       "│ 海通两融 ┆ 2023-10-09 ┆ 09:45:18 ┆ 000698   ┆ … ┆ 48.24  ┆ 0.0    ┆ 0.0    ┆ 96402.56   │\n",
       "│ 海通两融 ┆ 2023-10-09 ┆ 09:44:40 ┆ 605288   ┆ … ┆ 51.05  ┆ 1.0    ┆ 0.0    ┆ 102032.21  │\n",
       "└──────────┴────────────┴──────────┴──────────┴───┴────────┴────────┴────────┴────────────┘"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "84688f3d-b48d-437f-a585-794f31bc031c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.with_columns(\n",
    "#     成交金额2=pl.col(\"成交金额\") * pl.col(\"成交数量\"),\n",
    "# ).with_columns(\n",
    "#     成交金额D=pl.col(\"成交金额\") - pl.col(\"成交金额2\"),\n",
    "# ).with_columns(\n",
    "#     发生金额D=pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "#     .then(-pl.col(\"成交金额\"))\n",
    "#     .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "#     .then(pl.col(\"成交金额\")),\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "7d3ce5f9-fc45-4475-bbee-45a42b054142",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.write_parquet(\"stock_tradesparquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "30993a9b-971c-4a89-9063-ad093dd81cb1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 14)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th></tr><tr><td>str</td><td>date</td><td>time</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:38:10</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;卖出&quot;</td><td>13.2062</td><td>10400.0</td><td>137344.0</td><td>21.98</td><td>137.35</td><td>1.38</td><td>0.0</td><td>137184.67</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:44:52</td><td>&quot;600408&quot;</td><td>&quot;安泰集团&quot;</td><td>&quot;买入&quot;</td><td>3.19</td><td>47000.0</td><td>149930.0</td><td>23.99</td><td>0.0</td><td>1.51</td><td>0.0</td><td>-149955.5</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:44:31</td><td>&quot;600648&quot;</td><td>&quot;外高桥&quot;</td><td>&quot;买入&quot;</td><td>12.6066</td><td>11900.0</td><td>150019.0</td><td>24.0</td><td>0.0</td><td>1.49</td><td>0.0</td><td>-150044.49</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:43:38</td><td>&quot;600269&quot;</td><td>&quot;赣粤高速&quot;</td><td>&quot;买入&quot;</td><td>3.69</td><td>40700.0</td><td>150183.0</td><td>24.03</td><td>0.0</td><td>1.5</td><td>0.0</td><td>-150208.53</td></tr><tr><td>&quot;湘财&quot;</td><td>2022-07-18</td><td>09:42:51</td><td>&quot;600015&quot;</td><td>&quot;华夏银行&quot;</td><td>&quot;买入&quot;</td><td>5.07</td><td>30000.0</td><td>152100.0</td><td>24.34</td><td>0.0</td><td>1.52</td><td>0.0</td><td>-152125.86</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-18</td><td>09:46:15</td><td>&quot;300464&quot;</td><td>&quot;星徽股份&quot;</td><td>&quot;卖出&quot;</td><td>5.74</td><td>16100.0</td><td>92414.0</td><td>8.82</td><td>46.21</td><td>0.0</td><td>0.0</td><td>92358.97</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-18</td><td>09:55:41</td><td>&quot;002661&quot;</td><td>&quot;克明食品&quot;</td><td>&quot;买入&quot;</td><td>9.42</td><td>8500.0</td><td>80072.0</td><td>7.64</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-80079.64</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-09</td><td>09:48:02</td><td>&quot;002753&quot;</td><td>&quot;永东股份&quot;</td><td>&quot;买入&quot;</td><td>7.02</td><td>14200.0</td><td>99684.0</td><td>9.51</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-99693.51</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-09</td><td>09:45:18</td><td>&quot;000698&quot;</td><td>&quot;沈阳化工&quot;</td><td>&quot;卖出&quot;</td><td>4.053</td><td>23800.0</td><td>96460.0</td><td>9.2</td><td>48.24</td><td>0.0</td><td>0.0</td><td>96402.56</td></tr><tr><td>&quot;海通两融&quot;</td><td>2023-10-09</td><td>09:44:40</td><td>&quot;605288&quot;</td><td>&quot;凯迪股份&quot;</td><td>&quot;卖出&quot;</td><td>40.838</td><td>2500.0</td><td>102094.0</td><td>9.74</td><td>51.05</td><td>1.0</td><td>0.0</td><td>102032.21</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 14)\n",
       "┌──────────┬────────────┬──────────┬──────────┬───┬────────┬────────┬────────┬────────────┐\n",
       "│ 券商     ┆ 交易日期   ┆ 交易时间 ┆ 证券代码 ┆ … ┆ 印花税 ┆ 过户费 ┆ 其他费 ┆ 发生金额   │\n",
       "│ ---      ┆ ---        ┆ ---      ┆ ---      ┆   ┆ ---    ┆ ---    ┆ ---    ┆ ---        │\n",
       "│ str      ┆ date       ┆ time     ┆ str      ┆   ┆ f64    ┆ f64    ┆ f64    ┆ f64        │\n",
       "╞══════════╪════════════╪══════════╪══════════╪═══╪════════╪════════╪════════╪════════════╡\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:38:10 ┆ 002462   ┆ … ┆ 137.35 ┆ 1.38   ┆ 0.0    ┆ 137184.67  │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:44:52 ┆ 600408   ┆ … ┆ 0.0    ┆ 1.51   ┆ 0.0    ┆ -149955.5  │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:44:31 ┆ 600648   ┆ … ┆ 0.0    ┆ 1.49   ┆ 0.0    ┆ -150044.49 │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:43:38 ┆ 600269   ┆ … ┆ 0.0    ┆ 1.5    ┆ 0.0    ┆ -150208.53 │\n",
       "│ 湘财     ┆ 2022-07-18 ┆ 09:42:51 ┆ 600015   ┆ … ┆ 0.0    ┆ 1.52   ┆ 0.0    ┆ -152125.86 │\n",
       "│ …        ┆ …          ┆ …        ┆ …        ┆ … ┆ …      ┆ …      ┆ …      ┆ …          │\n",
       "│ 海通两融 ┆ 2023-10-18 ┆ 09:46:15 ┆ 300464   ┆ … ┆ 46.21  ┆ 0.0    ┆ 0.0    ┆ 92358.97   │\n",
       "│ 海通两融 ┆ 2023-10-18 ┆ 09:55:41 ┆ 002661   ┆ … ┆ 0.0    ┆ 0.0    ┆ 0.0    ┆ -80079.64  │\n",
       "│ 海通两融 ┆ 2023-10-09 ┆ 09:48:02 ┆ 002753   ┆ … ┆ 0.0    ┆ 0.0    ┆ 0.0    ┆ -99693.51  │\n",
       "│ 海通两融 ┆ 2023-10-09 ┆ 09:45:18 ┆ 000698   ┆ … ┆ 48.24  ┆ 0.0    ┆ 0.0    ┆ 96402.56   │\n",
       "│ 海通两融 ┆ 2023-10-09 ┆ 09:44:40 ┆ 605288   ┆ … ┆ 51.05  ┆ 1.0    ┆ 0.0    ┆ 102032.21  │\n",
       "└──────────┴────────────┴──────────┴──────────┴───┴────────┴────────┴────────┴────────────┘"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de40860b-8963-41e1-8376-d17cd3a222a4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
